package com.atguigu.utils;


import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.CaseFormat;
import org.apache.commons.beanutils.BeanUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 工具类目标:可以使用在任何JDBC数据库的任何查询语句  id为主键
 * select count(*) from t;                           单行单列
 * select * from t where id = '1001';                单行多列
 * select count(*) from t group by dept_id;          多行单列
 * select * from t;                                  多行多列
 * <p>
 * id,name,sex
 * 1 ,zs  ,male
 * 2 ,ls  ,female
 * <p>
 * ==>
 * T{id=1,name=zs,sex=male}
 * T{id=2,name=ls,sex=female}
 */
public class PhoenixUtil {

    public static <T> List<T> query(Connection connection, String sql, Class<T> clz, boolean isUnderScoreToCamel) throws Exception {

        ArrayList<T> list;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //创建集合用于存放查询结果数据
            list = new ArrayList<>();

            //预编译SQL
            preparedStatement = connection.prepareStatement(sql);

            //执行查询
            resultSet = preparedStatement.executeQuery();

            //获取元数据信息
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            //遍历resultSet,将每行数据转换为T对象并添加至集合  行遍历
            while (resultSet.next()) {

                //创建T对象
                T t = clz.newInstance();

                //列遍历
                for (int i = 1; i < columnCount + 1; i++) {

                    //获取列名与列值
                    String columnName = metaData.getColumnName(i);
                    Object value = resultSet.getObject(columnName);

                    //给T对象赋值
                    if (isUnderScoreToCamel) {
                        columnName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName.toLowerCase());
                    }

                    BeanUtils.setProperty(t, columnName, value);
                }

                //将T对象加入集合
                list.add(t);
            }
        } finally {
            //释放资源
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        //返回结果
        return list;
    }

    public static void main(String[] args) throws Exception {
        DruidDataSource dataSource = DruidDSUtil.createDataSource();
        DruidPooledConnection connection = dataSource.getConnection();
        List<JSONObject> list = query(connection,
                "select count(*) ct from GMALL211126_REALTIME.DIM_BASE_TRADEMARK",
                JSONObject.class,
                true);
        for (JSONObject jsonObject : list) {
            System.out.println(jsonObject);
        }
        connection.close();
        dataSource.close();
    }

}
